{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "5cb20b3326165fd",
   "metadata": {},
   "source": [
    "### 0.初始化：导包，创建文件夹"
   ]
  },
  {
   "cell_type": "code",
   "id": "7c9b1b47ec64885b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-08-23T07:14:11.313688Z",
     "start_time": "2024-08-23T07:14:11.294047Z"
    }
   },
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "import  warnings\n",
    "warnings.filterwarnings('ignore') # 忽略报错：python UserWarning: Workbook contains no default style, apply openpyxl‘s default\n",
    "\n",
    "folder_sources = './数据源'\n",
    "folder_result = './处理结果'\n",
    "\n",
    "folder_sources_TEMUSettlement = folder_sources+\"/TEMU结算数据/\"\n",
    "folder_sources_AMZReport = folder_sources+\"/AMZ后台报告/\"\n",
    "folder_sources_ERPData = folder_sources+\"/ERP数据/\"\n",
    "folder_sources_TEMUOrders = folder_sources+\"/TEMU订单/\"\n",
    "folder_sources_TEMUFine = folder_sources+\"/TEMU罚款/\"\n",
    "\n",
    "def create_folder(path: str):\n",
    "    if not os.path.exists(path):\n",
    "        os.mkdir(path)\n",
    "        print(f'{path} 文件夹创建成功！')\n",
    "    else:\n",
    "        print(f'{path} 文件夹已经存在！')\n",
    "\n",
    "create_folder(folder_sources)\n",
    "create_folder(folder_sources_TEMUSettlement)\n",
    "create_folder(folder_sources_AMZReport)\n",
    "create_folder(folder_sources_ERPData)\n",
    "create_folder(folder_sources_TEMUOrders)\n",
    "create_folder(folder_sources_TEMUFine)\n",
    "create_folder(folder_result)"
   ],
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "./数据源 文件夹已经存在！\n",
      "./数据源/TEMU结算数据/ 文件夹已经存在！\n",
      "./数据源/AMZ后台报告/ 文件夹已经存在！\n",
      "./数据源/ERP数据/ 文件夹已经存在！\n",
      "./数据源/TEMU订单/ 文件夹已经存在！\n",
      "./数据源/TEMU罚款/ 文件夹已经存在！\n",
      "./处理结果 文件夹已经存在！\n"
     ]
    }
   ],
   "execution_count": 18
  },
  {
   "cell_type": "markdown",
   "id": "5b458a876b005a46",
   "metadata": {},
   "source": [
    "### 第一步，合并TEMU各个店铺结算数据"
   ]
  },
  {
   "cell_type": "code",
   "id": "385d4462b8f2cf61",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-08-23T07:14:14.701511Z",
     "start_time": "2024-08-23T07:14:14.064590Z"
    }
   },
   "source": [
    "data_list = {}\n",
    "\n",
    "for fname in os.listdir(folder_sources_TEMUSettlement):\n",
    "    if fname.endswith(\".xlsx\"):\n",
    "        df_excel = pd.read_excel(folder_sources_TEMUSettlement + fname, sheet_name=None)\n",
    "        store_name = fname.split('-')[0]\n",
    "        # print(f'店铺名称：{store_name}')\n",
    "        for sheet_name, df_data in df_excel.items():\n",
    "            df_data.insert(0, column='店铺', value=store_name)\n",
    "\n",
    "            if sheet_name in data_list.keys():\n",
    "                data_list[sheet_name] = pd.concat([data_list[sheet_name], df_data], ignore_index=True)\n",
    "                # print('---------------')\n",
    "                # print('已经存在了，合并数据')\n",
    "            else:\n",
    "                data_list[sheet_name] = df_data\n",
    "\n",
    "            # print(f'表名：{sheet_name},行数：{df_data.count()}')\n",
    "\n",
    "for sheet_name, data in data_list.items():\n",
    "    data.to_csv(f\"{folder_result}/1.{sheet_name}.csv\", index=False)\n",
    "\n",
    "    print(f'合并 {sheet_name} 数据完成，数据量：{len(data)}条')"
   ],
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "合并 交易收入 数据完成，数据量：3022条\n",
      "合并 售后退款 数据完成，数据量：297条\n",
      "合并 运费收入 数据完成，数据量：1250条\n",
      "合并 运费退款 数据完成，数据量：47条\n"
     ]
    }
   ],
   "execution_count": 19
  },
  {
   "cell_type": "markdown",
   "id": "2a9e0360d77b6b08",
   "metadata": {},
   "source": [
    "### 2.合并TEMU各个店铺订单数据"
   ]
  },
  {
   "cell_type": "code",
   "id": "124846a4c282df10",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-08-23T07:14:20.115830Z",
     "start_time": "2024-08-23T07:14:19.571200Z"
    }
   },
   "source": [
    "data_list=[]\n",
    "\n",
    "for fname in os.listdir(folder_sources_TEMUOrders):\n",
    "    if fname.endswith(\".xlsx\"):\n",
    "        df_excel = pd.read_excel(folder_sources_TEMUOrders + fname)\n",
    "        store_name = fname.split('-')[0]\n",
    "        print(f'店铺名称：{store_name}, {len(df_excel)} 条')\n",
    "        df_excel.insert(0, column='店铺', value=store_name)\n",
    "        data_list.append(df_excel)\n",
    "\n",
    "data_all = pd.concat(data_list)\n",
    "data_all.to_csv(f\"{folder_result}/2.订单列表.csv\", index=False)\n",
    "print(f'合并 订单 数据完成，数据量：{len(data_all)}条')"
   ],
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "店铺名称：YOBTOP FACTORY, 16 条\n",
      "店铺名称：LOCAL JINH ART, 563 条\n",
      "店铺名称：GEEK CRAFT Limited, 4 条\n",
      "店铺名称：LOCAL EPRESINART, 1010 条\n",
      "店铺名称：LOCAL EPRESINART, 640 条\n",
      "店铺名称：BUTIRESIN FACTORY, 41 条\n",
      "店铺名称：EPRESINART FACTORY, 95 条\n",
      "店铺名称：LOCAL YOBTOP, 943 条\n",
      "店铺名称：LOCAL YOBTOP, 12 条\n",
      "店铺名称：JINH FACTORY, 39 条\n",
      "店铺名称：LOCAL BUTIRESIN ART, 865 条\n",
      "合并 订单 数据完成，数据量：4228条\n"
     ]
    }
   ],
   "execution_count": 20
  },
  {
   "cell_type": "markdown",
   "id": "a4f299f38c868740",
   "metadata": {},
   "source": [
    "### 3.将订单数据合并到结算数据中，根据订单编号匹配"
   ]
  },
  {
   "cell_type": "code",
   "id": "fa6033ef8c69cc22",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-08-23T07:14:23.314099Z",
     "start_time": "2024-08-23T07:14:23.240797Z"
    }
   },
   "source": [
    "\n",
    "\n",
    "order_list = pd.read_csv(folder_result + \"/2.订单列表.csv\")\n",
    "settled_list = pd.read_csv(folder_result + \"/1.交易收入.csv\")\n",
    "# pd.set_option(\"display.max_rows\", None, \"display.max_columns\", 100)\n",
    "\n",
    "print(f'结算数据：{len(settled_list)} 条，订单数据： {len(order_list)} 条')\n",
    "\n",
    "order_list.drop(\n",
    "    \"店铺,收货人姓名,收货人联系方式,详细地址1,详细地址2,详细地址3,区县,城市,省份,收货地址邮编,邮箱,国家\".split(','),\n",
    "    axis=1, inplace=True)\n",
    "\n",
    "pd.set_option(\"display.max_rows\", None, \"display.max_columns\", 100)\n",
    "\n",
    "result_list = pd.merge(settled_list, order_list, how='left', left_on=['订单编号', '子订单号'],\n",
    "                       right_on=['订单号', '子订单号'])\n",
    "result_list.drop('订单号', axis=1, inplace=True)\n",
    "\n",
    "# print(result_list.columns)\n",
    "\n",
    "# hebing_list = result_list.groupby(['订单编号','SKU货号'])[['交易收入','商品件数']].sum().reset_index()\n",
    "# print(hebing_list)\n",
    "\n",
    "# result_list = pd.merge(settled_list,hebing_list,how='right',left_on=['订单编号','子订单号'],right_on=['订单号','子订单号'])\n",
    "\n",
    "# hebing_list = result_list.groupby(['订单编号','SKU货号'])[['交易收入','商品件数','其他成本']].sum().reset_index()\n",
    "#\n",
    "result_list.to_csv(folder_result + \"/3.处理过的结算数据.csv\", index=False)\n",
    "\n",
    "print(f'合并完成，合并之后的数据量：{len(result_list)} 条.')\n",
    "#\n"
   ],
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "结算数据：3022 条，订单数据： 4228 条\n",
      "合并完成，合并之后的数据量：3022 条.\n"
     ]
    }
   ],
   "execution_count": 21
  },
  {
   "cell_type": "markdown",
   "id": "4c2e05f75481513f",
   "metadata": {},
   "source": [
    "### 4.将多渠道发货数据和第三方发货单数据合并到结算数据中\n",
    "#### 4.1根据`第三方发货单`中的`销售出库单号`和`多渠道发货单`数据中的`卖家订单号`匹配\n",
    "#### 4.2根据第`三方发货单`中的`平台单号`和`结算数据`中的`订单编号`匹配"
   ]
  },
  {
   "cell_type": "code",
   "id": "50d372db21d4a009",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-08-23T07:14:31.171879Z",
     "start_time": "2024-08-23T07:14:28.328409Z"
    }
   },
   "source": [
    "\n",
    "# amz_dir_path = p_path+\"/数据源/AMZ后台报告/\"\n",
    "#\n",
    "# amz_list_temp = []\n",
    "# for fname in os.listdir(amz_dir_path):\n",
    "#     if fname.endswith(\".txt\"):\n",
    "#         data = pd.read_csv(amz_list_temp+fname,sep='\\t',header=None)\n",
    "#         amz_list_temp.append(data)\n",
    "#\n",
    "# amz_list = pd.concat(amz_list_temp)\n",
    "# amz_list.to_excel(f\"{p_path}/处理结果/4.1AMZ配送明细.xlsx\", index=False)\n",
    "\n",
    "sale_list = pd.DataFrame()\n",
    "mcf_list = pd.DataFrame()\n",
    "\n",
    "for fname in os.listdir(folder_sources_ERPData):\n",
    "    # 通过发货单匹配领星系统编号\n",
    "    if fname.startswith('仓库发货') and fname.endswith(\".xlsx\"):\n",
    "        sale_list = pd.read_excel(folder_sources_ERPData + fname)\n",
    "        print(f'仓库发货订单数据：{len(sale_list)} 条')\n",
    "        sale_list = sale_list[\n",
    "            [\"销售出库单号\", \"平台单号\", \"SKU\", \"数量\"]]\n",
    "        pd.set_option('display.max_rows', 120, 'display.max_columns', None)\n",
    "        # da = sale_list['销售出库单号'] = sale_list['销售出库单号'].ffill()\n",
    "        # da = sale_list.ffill(axis=1,limit=1)\n",
    "        sale_list = sale_list.ffill()\n",
    "        print(f'仓库发货订单去重复之后的数据：{len(sale_list)} 条')\n",
    "        # print('------------------sale_list---------------------')\n",
    "        # print(sale_list.iloc[4390:4400])\n",
    "\n",
    "    if fname.startswith('多渠道订单') and fname.endswith(\".xlsx\"):\n",
    "        mcf_list = pd.read_excel(folder_sources_ERPData + fname)\n",
    "        print(f'多渠道发货订单数据：{len(mcf_list)} 条')\n",
    "        # print('------------------mcf_list---------------------')\n",
    "        # print(mcf_list)\n",
    "        # ddd = mcf_list.groupby(['卖家订单号','MSKU'])['FBA费']/mcf_list.groupby(['卖家订单号','MSKU'])['数量']\n",
    "        mcf_list['FBA DIV'] = mcf_list['FBA费'].div(mcf_list['数量'])\n",
    "\n",
    "        mcf_list = mcf_list[\n",
    "            [\"卖家订单号\", \"亚马逊订单号\", \"MSKU\", \"SKU\", \"数量\", \"FBA费\", \"FBA DIV\"]]\n",
    "        # print(mcf_list)\n",
    "\n",
    "pr = pd.merge(sale_list, mcf_list, how='outer', left_on=['销售出库单号', 'SKU'], right_on=['卖家订单号', 'SKU'])\n",
    "# print('------------------pr---------------------')\n",
    "# print(pr.iloc[1630:1640])\n",
    "pr = pr[[\"销售出库单号\", \"平台单号\", \"亚马逊订单号\", \"MSKU\", \"SKU\", \"FBA费\", \"FBA DIV\"]]\n",
    "pr.to_csv(f\"{folder_result}/4.领星-TEMU-AMZ单号合并.csv\", index=False)\n",
    "print(f'仓库发货和多渠道发货订单数据合并的数据：{len(pr)} 条')\n",
    "\n",
    "# # 将第四步合并的订单，合并到TEMU结算数据中\n",
    "order_list = pd.read_csv(f\"{folder_result}/4.领星-TEMU-AMZ单号合并.csv\")\n",
    "settled_list = pd.read_csv(f\"{folder_result}/3.处理过的结算数据.csv\")\n",
    "#\n",
    "result_list = pd.merge(settled_list, pr, how='left', left_on=['订单编号', 'SKU货号'], right_on=['平台单号', 'MSKU'])\n",
    "result_list = result_list.drop_duplicates()\n",
    "\n",
    "# print(result_list.columns)\n",
    "\n",
    "result_list.drop(['平台单号', 'SKU货号'], axis=1, inplace=True)\n",
    "result_list.to_csv(f\"{folder_result}/4.处理过的结算数据.csv\", index=False)\n",
    "\n",
    "print(f'合并到结算数据中的数据：{len(result_list)} 条')\n"
   ],
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "仓库发货订单数据：4846 条\n",
      "仓库发货订单去重复之后的数据：4846 条\n",
      "多渠道发货订单数据：6122 条\n",
      "仓库发货和多渠道发货订单数据合并的数据：6128 条\n",
      "合并到结算数据中的数据：3022 条\n"
     ]
    }
   ],
   "execution_count": 22
  },
  {
   "cell_type": "markdown",
   "id": "69399f71382dfad5",
   "metadata": {},
   "source": [
    "### 5.导入成本，根据`结算差异报告`中的订单号与`结算数据`中的`订单号`匹配\n",
    "    合并结算差异报告中相同订单号的数据\n",
    "    对结算差异报告中的采购成本，头程成本，其他成本根据订单数量进行计算"
   ]
  },
  {
   "cell_type": "code",
   "id": "56776dd027b61478",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-08-23T07:15:05.378448Z",
     "start_time": "2024-08-23T07:14:59.152937Z"
    }
   },
   "source": [
    "for fname in os.listdir(folder_sources_ERPData):\n",
    "    if fname.startswith('结算差异报告') and fname.endswith(\".xlsx\"):\n",
    "        js_list = pd.read_excel(folder_sources_ERPData + fname)\n",
    "        print(f'结算差异报告数据：{len(js_list)} 条')\n",
    "        sale_list = pd.read_csv(f\"{folder_result}/4.处理过的结算数据.csv\")\n",
    "        print(f'TEMU结算数据：{len(sale_list)} 条')\n",
    "\n",
    "        js_list = js_list.drop_duplicates()  # 去除重复数据\n",
    "        print(f'结算差异报告去除重复之后的数据：{len(js_list)} 条')\n",
    "        # 对结算差异表进行预处理，合并单号和成本\n",
    "        da = js_list.groupby(['订单号', 'SKU'])[['数量', '采购成本', '头程成本', '其他成本']].sum().reset_index()\n",
    "\n",
    "        da['采购成本'] = da['采购成本'].div(da['数量'])\n",
    "        da['头程成本'] = da['头程成本'].div(da['数量'])\n",
    "        da['其他成本'] = da['其他成本'].div(da['数量'])\n",
    "        print(f'结算差异报告合并单号和成本的数据：{len(da)} 条')\n",
    "\n",
    "        result_list = pd.merge(sale_list, da, how='left', left_on=[\"亚马逊订单号\", \"SKU\"],\n",
    "                               right_on=[\"订单号\", \"SKU\"])\n",
    "\n",
    "        result_list.drop(['订单号', \"数量\"], axis=1, inplace=True)\n",
    "\n",
    "        result_list.rename(columns={'币种': '结算币种', 'FBA费': '订单FBA费($)', '单个FBA费用($)': 'FBA DIV($)',\n",
    "                                    '采购成本': '采购成本(¥)', '头程成本': '头程成本(¥)', '其他成本': '其他成本(¥)'},\n",
    "                           inplace=True)\n",
    "        result_list.to_csv(f\"{folder_result}/5.处理过的结算数据.csv\", index=False)\n",
    "        print(f'合并完成的数据：{len(result_list)} 条')\n"
   ],
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "结算差异报告数据：20008 条\n",
      "TEMU结算数据：3022 条\n",
      "结算差异报告去除重复之后的数据：19715 条\n",
      "结算差异报告合并单号和成本的数据：19241 条\n",
      "合并完成的数据：3022 条\n"
     ]
    }
   ],
   "execution_count": 24
  },
  {
   "cell_type": "markdown",
   "id": "9a4689c12c39ee86",
   "metadata": {},
   "source": [
    "### 6.合并售后退款，运费收入，运费退款数据，并将合并后的数据合并到结算数据中"
   ]
  },
  {
   "cell_type": "code",
   "id": "88fc4bd20873a089",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-08-23T07:15:08.750468Z",
     "start_time": "2024-08-23T07:15:08.673371Z"
    }
   },
   "source": [
    "shtk_list = pd.read_csv(folder_result + \"/1.售后退款.csv\")\n",
    "yfsr_list = pd.read_csv(folder_result + \"/1.运费收入.csv\")\n",
    "yftk_list = pd.read_csv(folder_result + \"/1.运费退款.csv\")\n",
    "\n",
    "print(f'售后退款数据：{len(shtk_list)} 条')\n",
    "print(f'运费收入数据：{len(yfsr_list)} 条')\n",
    "print(f'运费退款数据：{len(yftk_list)} 条')\n",
    "\n",
    "sale_list = pd.read_csv(f\"{folder_result}/5.处理过的结算数据.csv\")\n",
    "print(f'TEMU结算数据：{len(sale_list)} 条')\n",
    "\n",
    "shtk_data = shtk_list.groupby(['订单编号', '子订单号', '币种'])['售后退款金额'].sum().reset_index()\n",
    "shtk_data.rename(columns={\"币种\":\"售后退款币种\"},inplace=True)\n",
    "shtk_data[\"售后退款金额\"]=-shtk_data[\"售后退款金额\"]\n",
    "# a = shtk_data.iloc[200:300]\n",
    "# shtk_data.to_excel(f\"{p_path}/处理结果/6.售后退款.xlsx\", index=False)\n",
    "\n",
    "# 合并运费收入和运费退款\n",
    "srtk_list = pd.merge(yfsr_list, yftk_list, how='left', left_on=[\"订单编号\"],\n",
    "                       right_on=[\"订单编号\"])\n",
    "srtk_list.drop(['店铺_x','账务时间_x','店铺_y','账务时间_y'], axis=1, inplace=True)\n",
    "srtk_list.rename(columns={\"币种_x\":\"运费收入币种\",\"币种_y\":\"运费退款币种\"},inplace=True)\n",
    "srtk_list[\"运费退款\"]=-srtk_list[\"运费退款\"]\n",
    "# result_list.to_excel(f\"{p_path}/处理结果/6.运费收入和退款.xlsx\", index=False)\n",
    "# 合并结算数据和售后退款\n",
    "result_list1 = pd.merge(sale_list, shtk_data, how='left', left_on=[\"订单编号\",\"子订单号\"],\n",
    "                       right_on=[\"订单编号\",\"子订单号\"])\n",
    "\n",
    "# 合并运费进结算数据\n",
    "result_list2 = pd.merge(result_list1, srtk_list, how='left', left_on=[\"订单编号\"],\n",
    "                       right_on=[\"订单编号\"])\n",
    "\n",
    "# print(result_list2)\n",
    "result_list2.to_csv(f\"{folder_result}/7.最终的结算数据.csv\", index=False)\n",
    "print(f'合并之后的数据：{len(result_list2)} 条')\n"
   ],
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "售后退款数据：297 条\n",
      "运费收入数据：1250 条\n",
      "运费退款数据：47 条\n",
      "TEMU结算数据：3022 条\n",
      "合并之后的数据：3022 条\n"
     ]
    }
   ],
   "execution_count": 25
  },
  {
   "cell_type": "markdown",
   "id": "34212efa86b46eb8",
   "metadata": {},
   "source": [
    "### 7.合并罚款数据到结算数据中"
   ]
  },
  {
   "cell_type": "code",
   "id": "5c4ea7274d94f00",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-08-23T07:15:29.762114Z",
     "start_time": "2024-08-23T07:15:29.578167Z"
    }
   },
   "source": [
    "data_list = []\n",
    "\n",
    "for fname in os.listdir(folder_sources_TEMUFine):\n",
    "    if fname.endswith(\".xlsx\"):\n",
    "        df_excel = pd.read_excel(folder_sources_TEMUFine + fname)\n",
    "        store_name = fname.split('-')[0]\n",
    "        # print(f'店铺名称：{store_name}, {len(df_excel)} 条')\n",
    "        df_excel.insert(0, column='店铺', value=store_name)\n",
    "        data_list.append(df_excel)\n",
    "\n",
    "data_all = pd.concat(data_list)\n",
    "print(f'合并之后的罚款数据：{len(data_all)} 条')\n",
    "\n",
    "# print(data_all.columns)\n",
    "data_all['支出金额'] = -data_all['支出金额']\n",
    "data_all = data_all[['订单编号', '支出金额', '币种']]\n",
    "data_all.rename(columns={'支出金额': '罚款金额', '币种': '罚款币种'}, inplace=True)\n",
    "\n",
    "sale_list = pd.read_csv(f\"{folder_result}/7.最终的结算数据.csv\")\n",
    "\n",
    "result_list = pd.merge(sale_list, data_all, how='left', left_on=[\"订单编号\"],\n",
    "                       right_on=[\"订单编号\"])\n",
    "\n",
    "# print(result_list.columns)\n",
    "\n",
    "result_list.to_csv(f\"{folder_result}/8.最终结算数据.csv\", index=False)\n",
    "print(f'合并之后的结算数据：{len(result_list)} 条')\n"
   ],
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "合并之后的罚款数据：46 条\n",
      "合并之后的结算数据：3023 条\n"
     ]
    }
   ],
   "execution_count": 27
  },
  {
   "metadata": {},
   "cell_type": "code",
   "outputs": [],
   "execution_count": null,
   "source": "",
   "id": "d3b550df64757d7"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
